Grant Permissions in SQL

The user installing Keyfactor Command needs permissions to administer the SQL server and add databases and users (logins) in SQL. Full sysadmin permissions are needed to upgrade from a previous version of Keyfactor Command if the user running the upgrade is not the same user who installed the previous version of Keyfactor Command.

Windows Authentication

If you opt to use Windows authentication for the Keyfactor Command connection to SQL during the installation, the user who installs Keyfactor Command needs appropriate permissions in SQL. To grant this, add the user who will install Keyfactor Command to the SQL server login list:

  1. On the SQL server open the SQL Server Management Studio, connect to the database, and open Security.
  2. Right-click on Logins and choose New Login.
  3. Select the Windows authentication radio button.
  4. Enter the domain name and user name of the administrative user who will be installing Keyfactor Command in the Login name field.
  5. On the Login Properties page for this user, open Server Roles and check either the sysadmin role or the dbcreator, public and securityadmin roles, depending on whether this is a new install or an upgrade (see above).
  6. Accept the remainder of the defaults and click OK.

Once Keyfactor Command has been deployed, the Windows user used for the install can be removed from the Logins under Security in the SQL Server Management Studio. Ongoing connectivity to the database is maintained using logins automatically created in SQL for the Keyfactor Command application pool users (see Create Service Accounts for Keyfactor Command) specifically for the purpose during the installation.

SQL Authentication

If you opt to use SQL authentication, appropriate permissions need to be granted to the SQL user entered in the initial connection dialog of the Keyfactor Command Configuration Wizard. You may choose to create (or use an existing) SQL user for the installation and create a separate SQL user for ongoing connectivity or use the same user for both purposes.

Note:  Your SQL server must be configured to support mixed mode authentication in order to use the SQL authentication option.

To create a new SQL user for the initial SQL connection:

  1. On the SQL server open the SQL Server Management Studio, connect to the database, and open Security.
  2. Right-click on Logins and choose New Login.
  3. Select the SQL Server authentication radio button.
  4. Enter a user name for the SQL user in the Login name field and enter and confirm a Password. You may wish to uncheck the User must change password at next login box.
  5. On the Login Properties page for this user, open Server Roles and check either the sysadmin role or the dbcreator, public and securityadmin roles, depending on whether this is a new install or an upgrade (see above).
  6. Accept the remainder of the defaults and click OK.

Once Keyfactor Command has been deployed, this SQL user may be removed if it is not also serving the role of providing ongoing connectivity. During installation, you enter a SQL user name and password for a login to maintain ongoing connectivity. If this login already exists in SQL, it will be granted appropriate permissions. If this login does not already exist in SQL, it will be created and granted appropriate permissions.

Note:  Automatically generated service accounts are not created with the db_owner role. Instead, a keyfactor_db_role is created and granted to the service accounts. This role has permission on each of the schemas (dbo, ssl, ssh, cms_agents, etc.) and permission on the encryption certificate.